package org.kjk.employee;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.kjk.common.DAO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component("employeeDao")
public class EmployeeDAOImpl implements DAO {

	@Autowired
	@Qualifier("jdbcTemplate")
	public JdbcTemplate jdbcTemplate;

	@Override
	public <T> int insert(T obj) {
		String sql = "insert into employees values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return jdbcTemplate.queryForInt(sql, ((EmployeeDTO) obj).getData());
	}

	@Override
	public <T> int update(T obj) {
		EmployeeDTO dto = (EmployeeDTO) obj;
		String sql = "update employees set EMPLOYEE_ID=?, FIRST_NAME=?, LAST_NAME=?, EMAIL=?, PHONE_NUMBER=?, HIRE_DATE=?, JOB_ID=?, SALARY=?,"
				+ "COMMISSION_PCT=?, MANAGER_ID=?, DEPARTMENT_ID=? where EMPLOYEE_ID=?";
		Object[] param = new Object[dto.getData().length + 1];
		for (int i = 0; i < dto.getData().length; i++) {
			param[i] = dto.getData()[i];
		}
		param[param.length - 1] = dto.getEmployeeId();
		return jdbcTemplate.queryForInt(sql, param);
	}

	@Override
	public <T> int delete(T obj) {
		String sql = "delete from employees where EMPLOYEE_ID=?";
		return jdbcTemplate.queryForInt(sql,
				new Object[] { ((EmployeeDTO) obj).getEmployeeId() });
	}

	public int deleteAll() {
		String sql = "delete from employees";
		return jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<EmployeeDTO> select(String sql, String[] param) {
		return jdbcTemplate.query(sql, param, new EmployeeRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<EmployeeDTO> selectAll() {
		String sql = "select * from employees";
		return jdbcTemplate.query(sql, new EmployeeRowMapper());
	}

	@Override
	public JSONObject selectJson(String sql, String[] param)
			throws JSONException {
		JSONObject json = new JSONObject();
		final JSONArray array = new JSONArray();
		json.put("result", array);
		jdbcTemplate.query(sql, param, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				JSONObject obj1 = new JSONObject();
				try {
					obj1.put("employeeId", rs.getInt("EMPLOYEE_ID"));
					obj1.put("firstName", rs.getString("FIRST_NAME"));
					obj1.put("lastName", rs.getString("LAST_NAME"));
					obj1.put("eMail", rs.getString("EMAIL"));
					obj1.put("phoneNumber", rs.getString("PHONE_NUMBER"));
					obj1.put("hireDate", rs.getString("HIRE_DATE"));
					obj1.put("jobId", rs.getString("JOB_ID"));
					obj1.put("salary", rs.getInt("SALARY"));
					obj1.put("commission", rs.getInt("COMMISSION_PCT"));
					obj1.put("managerId", rs.getInt("MANAGER_ID"));
					obj1.put("departmentId", rs.getInt("DEPARTMENT_ID"));
					array.put(obj1);
				} catch (JSONException e) {
					e.printStackTrace();
				}
			}
		});
		return json;
	}

}
